package it.fc.service.db.manager;

import it.fc.bean.RoleBean;
import it.fc.bean.SquadraBean;
import it.fc.bean.UserBean;
import it.fc.exceptions.FcException;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import org.apache.log4j.Logger;
import org.hibernate.Session;

//Metodi per utenti

public class DbUserManager extends DbManager {

    /**
     * User Management
     */
    private static Logger log = Logger.getLogger(DbUserManager.class);

    public void insertUser(UserBean user) throws FcException {

        Session session = getSessionFactory().getCurrentSession();

        session.beginTransaction();
        String accountId = UUID.randomUUID().toString();
        user.setUuid(accountId);
        session.save(user);
        session.beginTransaction().commit();

    }

    public void deleteUser(int idUser) throws FcException {

        Connection conn = null;

        try {
            StringBuffer query = new StringBuffer();
            query.append("DELETE FROM UTENTI ");
            query.append("WHERE ID =");
            query.append(idUser);

            log.info("delete user " + idUser);

            conn = getConnection();
            Statement stm = conn.createStatement();
            stm.execute(query.toString());
            log.debug("Cancellazione utente " + idUser + " avvenuto con successo!");

        } catch (SQLException e) {
            log.error("Errore in deleteUser >> " + e.getMessage());
            throw new FcException(e);
        } finally {
            try {
                conn.close();
                log.debug("Connection close");
            } catch (SQLException e) {
                log.error("Errore in deleteUser(closeConn) >> " + e.getMessage());
            }
        }

    }

    public void updateUser(UserBean user) throws FcException {

        SquadraBean squadra = null;
        Connection conn = null;

        try {
            conn = getConnection();
            StringBuffer query = new StringBuffer();

            query.append("UPDATE UTENTI ");
            query.append("SET NOME='");
            query.append(user.getNome());
            query.append("',");
            query.append("COGNOME='");
            query.append(user.getCognome());
            query.append("',");
            query.append("USERNAME='");
            query.append(user.getUsername());
            query.append("',");
            query.append("PASSWORD='");
            query.append(user.getPassword());
            query.append("',");
            query.append("EMAIL='");
            query.append(user.getEmail());
            query.append("',");
            query.append("ATTIVATO=");
            query.append(user.isAttivato());
            query.append(",");
            query.append("BUDGET=");
            query.append(user.getBudget());
            query.append(" WHERE ID =");
            query.append(user.getIdUser());

            log.debug("Update user calling : " + query);
            Statement stm = conn.createStatement();
            stm.executeUpdate(query.toString());

            log.debug("Update user : " + user.getUsername());

        } catch (SQLException e) {
            log.error("Errore in updateUser >> " + e.getMessage());
            throw new FcException(e);
        } finally {
            try {
                conn.close();
                log.debug("Connection close");
            } catch (SQLException e) {
                log.error("Errore in updateUser(closeConn) >> " + e.getMessage());
            }
        }
    }

    public UserBean findUserByUsername(String username) throws FcException {

        Connection conn = null;
        UserBean bean = null;

        try {
            StringBuffer query = new StringBuffer();
            query.append("SELECT * FROM UTENTI ");
            query.append("WHERE USERNAME = '");
            query.append(username);
            query.append("' AND ATTIVATO = 1;");

            log.debug("findUserByUsername user :" + query);
            conn = getConnection();
            Statement stm = conn.createStatement();
            ResultSet res = stm.executeQuery(query.toString());

            log.debug("Search user : " + username + " ...");

            if (res.next()) {
                bean = new UserBean();
                bean.setIdUser(res.getInt("id"));
                bean.setNome(res.getString("nome"));
                bean.setCognome(res.getString("cognome"));
                bean.setPassword(res.getString("password"));
                bean.setEmail(res.getString("email"));
                bean.setUsername(res.getString("username"));
                bean.setAttivato(res.getBoolean("attivato"));
                bean.setBudget(Integer.parseInt(res.getString("budget")));

                StringBuffer strRuoli = new StringBuffer();

                strRuoli.append("SELECT * FROM UTENTI_RUOLI, RUOLI ");
                strRuoli.append("WHERE UTENTI_RUOLI.IDRUOLO = RUOLI.ID AND ");
                strRuoli.append("UTENTI_RUOLI.IDUTENTE = " + bean.getIdUser());

                log.debug("Call query " + strRuoli);
                ResultSet resRole = stm.executeQuery(strRuoli.toString());

                List<RoleBean> ruoli = new ArrayList<RoleBean>();
                while (resRole.next()) {
                    RoleBean ruolo = new RoleBean();
                    ruolo.setIdRole(resRole.getInt("id"));
                    ruolo.setNome(resRole.getString("nome"));
                    ruoli.add(ruolo);
                }

                log.debug("Parsing roles for user : " + bean.getUsername() + " role find (" + ruoli.size() + ");");
                bean.setRuoli(ruoli);
            }

        } catch (SQLException e) {
            log.error("Errore in findUserByUsername >> " + e.getMessage());
            throw new FcException(e);
        } finally {
            try {
                conn.close();
                log.debug("Connection close");
            } catch (SQLException e) {
                log.error("Errore in findUserByUsername(closeConn) >> " + e.getMessage());
            }
        }

        return bean;

    }

    public List<UserBean> getUsers() throws FcException {

        List<UserBean> listUsers = new ArrayList<UserBean>();
        String query = "SELECT * FROM UTENTI";

        Connection conn = null;

        try {
            conn = getConnection();
            Statement stm = conn.createStatement();
            ResultSet res = stm.executeQuery(query);

            while (res.next()) {
                UserBean utente = new UserBean();
                utente.setIdUser(res.getInt("id"));
                utente.setNome(res.getString("nome"));
                utente.setCognome(res.getString("cognome"));
                utente.setPassword(res.getString("password"));
                utente.setEmail(res.getString("email"));
                utente.setUsername(res.getString("username"));

                listUsers.add(utente);
            }

        } catch (SQLException e) {
            log.error("Errore in getUsers >> " + e.getMessage());
            throw new FcException(e);
        } finally {
            try {
                conn.close();
                log.debug("Connection close");
            } catch (SQLException e) {
                log.error("Errore in getUsers(closeConn) >> " + e.getMessage());
            }
        }

        return listUsers;
    }

    public void activateUser(String uuid) {

        Connection conn = null;

        try {
            StringBuffer query = new StringBuffer();
            query.append("UPDATE UTENTI SET ATTIVATO = 1 WHERE UUID = '");
            query.append(uuid);
            query.append("';");

            log.debug("activateUser user :" + query);
            conn = getConnection();
            Statement stm = conn.createStatement();
            stm.executeUpdate(query.toString());
        } catch (SQLException e) {
            log.error("Errore in activateUser >> " + e.getMessage());
        } finally {
            try {
                conn.close();
                log.debug("Connection close");
            } catch (SQLException e) {
                log.error("Errore in activateUser(closeConn) >> " + e.getMessage());
            }
        }

    }

}
